SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision
Posted
by pinaldave
on SQL Authority
See other posts from SQL Authority
or by pinaldave
Published on Tue, 01 Jun 2010 01:30:28 +0000
Indexed on
2010/06/01
1:35 UTC
Read the original article
Hit count: 811
Pinal Dave
|sql
|SQL Authority
|SQL DateTime
|SQL Query
|SQL Scripts
|SQL Server
|SQL Tips and Tricks
|T SQL
|Technology
I am myself surprised that I am writing this post today. I am going to present one of the very known facts of SQL Server SMALLDATETIME datatype. Even though this is a very well-known datatype, many a time, I have seen developers getting confused with precision of the SMALLDATETIME datatype.
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero. Let us see the following example
DECLARE @varSDate AS SMALLDATETIME
SET @varSDate = '1900-01-01 12:12:01'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:29'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:30'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:59'
SELECT @varSDate C_SDT
Following is the result of the above script and note that any value between 0 (zero) and 59 is converted up or down.
The part that confuses the developers is the value of the seconds in the display. I think if it is not maintained or recorded, it should not be displayed as well.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
© SQL Authority or respective owner
